package org.duang.db.sql;

import java.sql.Connection;
import java.sql.ParameterMetaData;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.LinkedHashMap;
import java.util.List;
import java.util.Map;


import org.duang.kit.ToolsKit;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;

/**
 * 数据库执行器
 * @author laotang
 *@since 1.0
 */
public final class DBRunner {

	private static Logger logger = LoggerFactory.getLogger(DBRunner.class);
	private Connection connection;
	private final static StringBuilder loggerStr = new StringBuilder();
	
	public DBRunner(Connection connection) {
		this.connection = connection;
	}
	
	/**
	 * 执行查询SQL语句
	 * @param sql			查询SQL语句			
	 * @param params		查询参数
	 * @return				结果集List<Map<String,Object>> 
	 * 							每一个Map代表着一行数据，Map里的key<==>字段名，value<==>字段值
	 * @throws SQLException
	 */
	public List<Map<String,Object>> query(String sql,  Object... params) throws SQLException {
		return this.query(sql, null, params);
	}
	
	/**
	 * 执行查询SQL语句
	 * @param sql				查询SQL语句
	 * @param columns			返回指定的字段			
	 * @param params			查询参数
	 * @return					结果集List<Map<String,Object>> 
	 * 								每一个Map代表着一行数据，Map里的key<==>字段名，value<==>字段值
	 * @throws SQLException
	 */
	public List<Map<String,Object>> query(String sql,  String[] columns, Object... params) throws SQLException {
		if(ToolsKit.isEmpty(connection)){  throw new SQLException("Null connection");}
		if(ToolsKit.isEmpty(sql)) {
			if(ToolsKit.isNotEmpty(connection)){ connection.close();}
			throw new SQLException("Null SQL statement");
		}
		loggerSql(sql, params);
		boolean isFilerColumn = ToolsKit.isNotEmpty(columns);
		if(isFilerColumn){ logger.info("column: " +  ToolsKit.toJsonString(columns));}
		
		List<Map<String,Object>> resultList = new ArrayList<Map<String,Object>>();
		PreparedStatement stmt = null;
        try {
            stmt = fillStatement(connection.prepareStatement(sql), params);
            ResultSet rs = stmt.executeQuery();
            if(ToolsKit.isEmpty(rs)) return null;
            ResultSetMetaData rsmd = rs.getMetaData();
            int cols = rsmd.getColumnCount();
            while(rs.next()) {
	            Map<String,Object> resultMap = new LinkedHashMap<String, Object>(cols);
	            for(int i=1; i<=cols; i++){
	            	 String columnName = rsmd.getColumnLabel(i);
	                 if (ToolsKit.isEmpty(columnName)) {
	                   columnName = rsmd.getColumnName(i);
	                 }
	                 if(isFilerColumn){
	                	 for(String column : columns){
	                		 if(column.equals(columnName)){
	                			 resultMap.put(columnName, rs.getObject(column));
	                			 break;
	                		 }
	                	 }
	                 } else {
	                	 resultMap.put(columnName, rs.getObject(i));
	                 }
	            }
	            if(ToolsKit.isNotEmpty(resultMap)){
	            	resultList.add(resultMap);
	            }
            }
        } catch(SQLException e){
			logger.warn("query "+sql+" error: " + e.getMessage(), e);
		} finally{
			DBSession.close(stmt);
			DBSession.close(connection);
		}
        return resultList;
	}
	
	/**
	 * 执行不带参数的SQL语句，如创建表及创建表索引时用
	 * @param sql
	 * @return
	 * @throws SQLException
	 */
	public int execute(String sql) throws SQLException{
		return execute(sql, DBSession.NULL_OBJECT);
	}
	
	/**
	 * 执行SQL语句，用于update, delete等
	 * @param sql						sql语句
	 * @param params				参数
	 * @return								受影响的行数
	 * @throws SQLException
	 */
	public int execute(String sql, Object... params) throws SQLException{
		if(ToolsKit.isEmpty(connection)){  throw new SQLException("Null connection");}
		if(ToolsKit.isEmpty(sql)) {
			if(ToolsKit.isNotEmpty(connection)){ connection.close();}
			throw new SQLException("Null SQL statement");
		}
		int rows = 0;
		PreparedStatement stmt = null;
		try{
			loggerSql(sql, params);
			stmt = fillStatement(connection.prepareStatement(sql), params);
			rows = stmt.executeUpdate();
		} catch(SQLException e){
			logger.warn("execute "+sql+" error: " + e.getMessage(), e);
		} finally{
			DBSession.close(stmt);
			DBSession.close(connection);
		}
		return rows;
	}
	
	private void loggerSql(String sql, Object... params) {
//		loggerStr.delete(0, loggerStr.length()); //时间长了会出现length()为负数的可能性
		loggerStr.setLength(0);
		loggerStr.append(sql);		
		if(ToolsKit.isNotEmpty(params)){ loggerStr.append("             ").append(ToolsKit.toJsonString(params));}
		logger.info(loggerStr.toString());
	}
	
	
	private PreparedStatement fillStatement (PreparedStatement stmt, Object... params) throws SQLException{
		if(ToolsKit.isNotEmpty(params)){
			
			ParameterMetaData pmd = stmt.getParameterMetaData();
            int stmtCount = pmd.getParameterCount();
            int paramsCount = params == null ? 0 : params.length;

            if (stmtCount != paramsCount) {
                throw new SQLException("Wrong number of parameters: expected "
                        + stmtCount + ", was given " + paramsCount);
            }
			
			for(int i =0; i<paramsCount; i++){
				stmt.setObject(i+1, params[i]);
			}
		}
		return stmt;
	}
	
}
